﻿using System;
using System.Data;
using VegetableSale.NET.Entities;
using VegetableSale.NET.Utils;

namespace VegetableSale.NET.Dao
{
	public class ProviderMapper
	{
        public List<Provider> getProviderList(QueryDTO queryDTO)
        {

            String sql = "";

            if (queryDTO.Keyword.Equals(""))
            {
                sql = "select * from provider;";
            }
            else
            {
                sql = "SELECT * FROM provider WHERE pvename='" + queryDTO.Keyword + "';";
            }

            List<Provider> providers = new List<Provider>();

            DataSet dateSet = MySqlHelper.GetDataSet(sql);
            DataTable dataTable = dateSet.Tables[0];

            foreach (DataRow row in dataTable.Rows)
            {
                uint providerid = (uint)row[0];
                string pvename = (string)row[1];
                string pvephone = (string)row[2];
                string pveaddress = (string)row[3];

                Provider provider = new Provider(providerid, pvename, pvephone, pveaddress);
                providers.Add(provider);
            }
            return providers;
        }

        public List<Provider> allProvider()
        {
           String sql = "select * from provider;";
           
           List<Provider> providers = new List<Provider>();

           DataSet dateSet = MySqlHelper.GetDataSet(sql);
           DataTable dataTable = dateSet.Tables[0];

           foreach (DataRow row in dataTable.Rows)
           {
               uint providerid = (uint)row[0];
               string pvename = (string)row[1];
               string pvephone = (string)row[2];
               string pveaddress = (string)row[3];

               Provider provider = new Provider(providerid, pvename, pvephone, pveaddress);
               providers.Add(provider);
           }
           return providers;
        }

        public void addProvider(Provider provider)
        {
            String sql = "INSERT INTO provider (pvename,pvephone,pveaddress) VALUES ('" + provider.Pvename + "','" + provider.Pvephone + "','" + provider.Pveaddress + "');";

            MySqlHelper.ExecuteSql(sql);
        }

        public void deleteProvider(int id)
        {
            String sql = "DELETE FROM provider WHERE providerid=" + id + ";";

            MySqlHelper.ExecuteSql(sql);
        }

        public void updateProvider(Provider provider)
        {
            String sql = "UPDATE provider SET pvename='" + provider.Pvename + "',pvephone='" + provider.Pvephone + "',pveaddress='" + provider.Pveaddress + "' WHERE providerid=" + provider.Providerid + ";";

            MySqlHelper.ExecuteSql(sql);

        }
        
    }
}

